package sample;

import java.sql.*;
import java.util.Properties;

public class DbTool3 {
    static {
        try {
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
        } catch (Exception e) {
            throw new RuntimeException(e.getMessage());
        }
    }


    public static Connection getConnection(String path) {
        Connection connection = null;
        try {
            //String url = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=D:\\test.mdb";
            String url = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=" + path;
            //Access中的数据库默认编码为GBK，本地项目为UTF-8，若不转码会出现乱码
            Properties p = new Properties();
            p.put("charSet", "GBK");
            connection = DriverManager.getConnection(url,p);
        } catch (SQLException e) {
            //SBTFxDialog.showConfirmDialog(null, e.getMessage(), "【ERROR】");
            e.printStackTrace();
        }
        return connection;
    }

    public static Statement getStatement(String path) {
        Connection connection = null;
        Statement statement = null;
        connection = getConnection(path);
        try {
            return connection.createStatement();
        } catch (SQLException e) {
            SBTFxDialog.showConfirmDialog(null, e.getMessage(), "【ERROR】");
        }
        return null;
    }



    /**
     * 执行update SQL
     * @param path  mdb文件路径
     * @param sql  udpate sql
     * @return
     */
    public static int exeUpdateSql(String path, String sql) {
        Statement statement = null;
        int result = 0;
        try {
            result = statement.executeUpdate(sql);
            return result;
        } catch (SQLException e) {
            SBTFxDialog.showConfirmDialog(null, e.getMessage(), "【ERROR】");
        } finally {
            close(null,statement,null);
        }
        return result;
    }

    /**
     * 执行查询 SQL 要关闭 示例 Dbtool.close(null,null,rs)
     * @param path  mdb文件路径
     * @param sql  udpate sql
     * @return
     */
    public static ResultSet exeQuerySql(String path, String sql) {
        Statement statement = null;
        Connection connection = null;
        try {
            statement = getStatement(path);
            ResultSet resultSet = statement.executeQuery(sql);
            return resultSet;
        } catch (SQLException e) {
            SBTFxDialog.showConfirmDialog(null, e.getMessage(), "【ERROR】");
        }
        return null;
    }

    public static void close(Connection connection, Statement statement, ResultSet rs) {
        try {
            if (rs != null) {
                statement = rs.getStatement();
                connection = statement.getConnection();
                rs.close();
            }
            if (null != statement) {
                connection = statement.getConnection();
                statement.close();
            }
            if (null != connection) {
                connection.close();
            }
        } catch (SQLException e) {
            SBTFxDialog.showConfirmDialog(null, e.getMessage(), "【ERROR】");
        }
    }



    public static void main(String[] args) throws SQLException {
        String path = "D:/test22/ahsx201801.mdb";
        String ywSql = "SELECT count(1) as sum from yw";
        ResultSet rs = exeQuerySql(path, ywSql);
        if (null != rs && rs.next()) {
            System.out.println(rs.getInt(0));
        }
        close(null,null,rs);
    }
}
